package com.northpool.operator.statistics.dataset;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.northpool.operator.annotation.InputParam;
import com.northpool.operator.annotation.InputParams;
import com.northpool.operator.annotation.OperatorInfo;
import com.northpool.operator.annotation.OutputParam;
import com.northpool.operator.annotation.OutputParams;
import com.northpool.operator.common.algorithm.OperatorTestBuild;
import com.northpool.operator.common.algorithm.TestParamBuilder;
import com.northpool.operator.common.database.DatabaseUtils;
import com.northpool.operator.param.ParamTool;
import com.northpool.operator.type.TypeEnum;
import com.northpool.operator.utils.StpOperatorHelper;
import com.northpool.resources.datasource.IDataSource;
import com.northpool.resources.datasource.db.DbDataSource;
import org.apache.commons.lang3.StringUtils;

import java.util.LinkedHashSet;
import java.util.Set;

@OperatorInfo(
        ref = "Json2DbOperator",
        name = "JSON数据入库",
        version = "1.0.0",
        description = "单层JSON数据入库，不支持多层的JSON结构"
)
@OutputParams({
        @OutputParam(ref = "dataset", name = "结果数据集"),
        @OutputParam(ref = "dataSourceId", name = "数据源ID"),
        @OutputParam(ref = "tableName", name = "结果表名称"),
})
public class Json2DbOperator extends StpOperatorHelper {
    String dataSourceId;
    String tableName;
    String json;
    String keyName;
    String valueName;
    Boolean replaceTable;

    @InputParams({
            @InputParam(ref = "dataSourceId", name = "数据源ID", desc = "数据源ID", type = TypeEnum.DATASOURCE, required = true),
            @InputParam(ref = "tableName", name = "数据表名称", desc = "数据表名称，如: public.table", type = TypeEnum.STRING, required = true),
            @InputParam(ref = "replaceTable", name = "是否重建表", desc = "如果存在是否重建表", type = TypeEnum.BOOL, required = true,value = "false"),
            @InputParam(ref = "keyName", name = "键名称", desc = "JSON中key, 用于建表", type = TypeEnum.STRING, required = true,value = "false"),
            @InputParam(ref = "valueName", name = "值名称", desc = "JSON中value，用于建表", type = TypeEnum.STRING, required = true,value = "false"),
            @InputParam(ref = "json", name = "JSON字符串", desc = "JSON字符串数组，示例：{\"轨道交通用地\":\"0.000009846945475028683965\"}", type = TypeEnum.STRING, required = true),
    })
    public Json2DbOperator() {
    }

    public static void main(String[] args) throws Exception {
        OperatorTestBuild.execute(Json2DbOperator.class, TestParamBuilder.aBuilder()
                .param("dataSourceId", "storage")
                .param("tableName", "result.结果入库测试")
                .param("replaceTable", "true")
                .param("keyName", "地类名称")
                .param("valueName", "面积（平方米）")
                .param("json", "{\"轨道交通用地\":0.000009846945475028683965,\"灌木林地\":0.000007361178690017387681,\"内陆滩涂\":0.00000678835127657202078,\"河流水面\":0.000225360217926400387436,\"机关团体新闻出版用地\":0.000158367771406240339197,\"特殊用地\":0.000150380973742464956441,\"旱地\":0.000048754824379765985646,\"科教文卫用地\":0.000245260347287173735404,\"铁路用地\":0.0001178013756468989572,\"其他草地\":0.000100434645858164675804,\"公用设施用地\":0.000055495447554980993922,\"水工建筑用地\":0.000048482106403414953536,\"果园\":0.00001536300990054374104,\"水浇地\":0.000156487871714635691334,\"农村道路\":0.000010078729813708403148,\"坑塘水面\":0.000040436444228147465126,\"公园与绿地\":0.000193764746225683371248,\"裸土地\":1.3877277549638002E-7,\"其他林地\":0.000045940169658849845954,\"乔木林地\":0.000031275203678607107794,\"高教用地\":0.00019439391478185896502,\"茶园\":0.0000016878950321734418,\"广场用地\":0.00001124851781028754522,\"沟渠\":0.000007051930584477605764,\"公路用地\":0.000030980667433908186610,\"水库水面\":0.0000231809498929163964,\"城镇村道路用地\":0.000599235702654177529316,\"水田\":0.0000340162616549899821975,\"交通服务场站用地\":0.000058763963556325039659,\"商业服务业设施用地\":0.000368386878465910568911,\"采矿用地\":6.230881125717021E-7,\"农村宅基地\":0.000230904004084421252853,\"城镇住宅用地\":0.0010801352024665461742616,\"其他园地\":0.00000325974846703728672,\"设施农用地\":8.809781273720383E-7,\"物流仓储用地\":0.00004774723303269513163,\"工业用地\":0.000159249132560190475016}")
                .build());
    }

    @Override
    public void execute(ParamTool paramTool) throws Exception {
        this.dataSourceId = paramTool.getStr("dataSourceId");
        this.tableName = paramTool.getStr("tableName");

        // 输出结果数据集
        this.result("tableName", this.tableName);
        this.result("dataSourceId", this.dataSourceId);
        this.result("dataset", DatabaseUtils.buildDatasetJson(this.dataSourceId, this.tableName));

        this.json = paramTool.getStr("json");
        this.keyName = paramTool.getStr("keyName");
        this.valueName = paramTool.getStr("valueName");
        this.replaceTable = paramTool.getBool("replaceTable");

        String dataSourceJson = DatabaseUtils.getDataSourceJson(this.dataSourceId);
        if (StringUtils.isBlank(dataSourceJson)) {
            throw new RuntimeException("数据源未找到： " + this.dataSourceId);
        }

        JSONObject object = JSONArray.parseObject(this.json);
        boolean empty = object.isEmpty();
        if (empty) {
            throw new RuntimeException("JSON数据为空");
        }

        DbDataSource dbDataSource = (DbDataSource) IDataSource.fromJSON(dataSourceJson);
        if (this.replaceTable && dbDataSource.hasTable(this.tableName)) {
            dbDataSource.drop(this.tableName);
        }
//        ITableOperator tableOperator = dbDataSource.getTableOperator(this.tableName);
        Set<String> fields = new LinkedHashSet<>();
        fields.add(keyName);
        fields.add(valueName);

        if (tableName.contains(".")) {
            tableName = tableName.replace(".", "\".\"");
        }
        tableName = "\"" + tableName + "\"";

        StringBuilder createTableSql = new StringBuilder("CREATE TABLE IF NOT EXISTS ");
        createTableSql.append(tableName).append(" (");
        // 遍历字段并生成列定义
        createTableSql.append("\"").append(keyName).append("\" VARCHAR(512), ");
        createTableSql.append("\"").append(valueName).append("\" VARCHAR(512))");

        dbDataSource.executeSql(createTableSql.toString());

        // 插入数据
        Set<String> keySet = object.keySet();
        for (String key : keySet) {
            StringBuilder insertSql = new StringBuilder("INSERT INTO ").append(tableName).append(" (");

            // 添加字段名
            for (String field : fields) {
                insertSql.append("\"").append(field).append("\", ");
            }

            // 去掉最后一个逗号和空格
            insertSql.setLength(insertSql.length() - 2);
            insertSql.append(") VALUES (");

            // 添加字段值
            insertSql.append("'").append(key).append("', ");
            insertSql.append("'").append(object.getString(key)).append("')");

            // 去掉最后一个逗号和空格

            dbDataSource.executeSql(insertSql.toString());
        }

        this.successExit();
    }

}
